from sqlalchemy.orm import Session
from sqlalchemy import and_
from app.models import models
from app.auth.auth import get_password_hash, verify_password
from datetime import datetime
from typing import Optional
from sqlalchemy import Column
from app.schemas import schemas


# 用户CRUD
def get_user_by_username(db: Session, username: str):
    return db.query(models.User).filter(models.User.username == username).first()


def create_user(db: Session, user: schemas.UserCreate):
    hashed_password = get_password_hash(user.password)
    db_user = models.User(
        username=user.username, password_hash=hashed_password, role=user.role
    )
    db.add(db_user)
    db.commit()
    db.refresh(db_user)
    return db_user


def authenticate_user(db: Session, username: str, password: str):
    user = get_user_by_username(db, username)
    if not user or not verify_password(password, user.password_hash):
        return False
    return user


# 学生CRUD
def get_students(db: Session, skip: int = 0, limit: int = 100):
    return db.query(models.Student).offset(skip).limit(limit).all()


def get_student(db: Session, student_id: Column[int] | int):
    return db.query(models.Student).filter(models.Student.id_ == student_id).first()


def get_student_by_student_id(db: Session, student_id: str):
    return (
        db.query(models.Student).filter(models.Student.student_id == student_id).first()
    )


def create_student(db: Session, student: schemas.StudentCreate):
    db_student = models.Student(**student.dict())
    db.add(db_student)
    db.commit()
    db.refresh(db_student)
    return db_student


def delete_student(db: Session, student_id: Column[int] | int):
    db.query(models.ComputerSession).filter(
        models.ComputerSession.student_id == student_id
    ).delete()
    db.query(models.StudentRecharge).filter(
        models.StudentRecharge.student_id == student_id
    ).delete()
    student = get_student(db, student_id)
    if student is None:
        return None
    db.delete(student)
    db.commit()
    return student


def recharge_student(db: Session, recharge: schemas.RechargeCreate, user_id: int):
    # 更新学生余额
    student = get_student(db, recharge.student_id)
    if not student:
        return None

    student.balance += recharge.amount

    # 创建充值记录
    db_recharge = models.StudentRecharge(
        student_id=recharge.student_id,
        amount=recharge.amount,
        recharged_by=user_id,
        notes=recharge.notes,
    )
    db.add(db_recharge)
    db.commit()
    db.refresh(db_recharge)
    return db_recharge


def get_recharges(db: Session, student_id: Column[int] | int):
    return (
        db.query(models.StudentRecharge)
        .filter(models.StudentRecharge.student_id == student_id)
        .all()
    )


# 机房CRUD
def get_computer_rooms(db: Session, skip: int = 0, limit: int = 100):
    return db.query(models.ComputerRoom).offset(skip).limit(limit).all()


def get_computer_room(db: Session, room_id: Column[int] | int):
    return (
        db.query(models.ComputerRoom).filter(models.ComputerRoom.id_ == room_id).first()
    )


def create_computer_room(db: Session, room: schemas.ComputerRoomCreate):
    db_room = models.ComputerRoom(
        **room.dict(), available_computers=room.total_computers
    )
    db.add(db_room)
    db.commit()
    db.refresh(db_room)
    return db_room


def update_computer_room_rate(db: Session, room_id: int, rate: float):
    room = get_computer_room(db, room_id)
    if room is None:
        return None
    room.hourly_rate = rate
    db.commit()
    db.refresh(room)
    return room


def update_computer_room_status(
    db: Session, room_id: int, status: str
) -> Optional[models.ComputerRoom]:

    if status not in [
        models.RoomStatus.active,
        models.RoomStatus.closed,
        models.RoomStatus.maintenance,
    ]:
        return None
    room = get_computer_room(db, room_id)
    if room is None:
        return None

    room.status = status
    db.commit()
    db.refresh(room)
    return room


def delete_computer_room(db: Session, room_id: int):
    room = get_computer_room(db, room_id)
    if room is None:
        return None
    db.query(models.ComputerSession).filter(
        models.ComputerSession.room_id == room_id
    ).delete()
    db.delete(room)
    db.commit()
    return room

# 机器CRUD
def get_machines(db: Session, skip: int = 0, limit: int = 100):
    return db.query(models.Machine).offset(skip).limit(limit).all()


def get_machine(db: Session, machine_id: int):
    return db.query(models.Machine).filter(models.Machine.id_ == machine_id).first()


def get_available_machines(db: Session, room_id: Optional[int]):
    query = db.query(models.Machine).filter(
        models.Machine.status == models.MachineStatus.available
    )
    if room_id:
        query = query.filter(models.Machine.room_id == room_id)
    return query.all()


def create_machine(db: Session, machine: schemas.MachineCreate):
    db_machine = models.Machine(**machine.dict())
    db.add(db_machine)
    db.commit()
    db.refresh(db_machine)
    return db_machine


def delete_machine(db: Session, machine_id: int):
    machine = get_machine(db, machine_id)
    if machine is None:
        return None
    db.query(models.ComputerSession).filter(
        models.ComputerSession.machine_id == machine_id
    ).delete()
    db.delete(machine)
    db.commit()
    return machine


def update_machine_status(
    db: Session, machine_id: int, status: str
) -> Optional[models.Machine]:

    if status not in [
        models.MachineStatus.available,
        models.MachineStatus.in_use,
        models.MachineStatus.maintenance,
    ]:
        return None
    machine = db.query(models.Machine).filter(models.Machine.id_ == machine_id).first()
    if machine is None:
        return None
    machine.status = status
    db.commit()
    db.refresh(machine)
    return machine


# 上机会话CRUD
def start_computer_session(db: Session, session_data: schemas.SessionStart):
    # 检查学生余额
    student = get_student(db, session_data.student_id)
    assert student is not None
    if student is None:
        return None
    if student.balance <= 0:
        return None

    # 检查机器状态
    machine = (
        db.query(models.Machine)
        .filter(models.Machine.id_ == session_data.machine_id)
        .first()
    )
    if not machine or (machine.status != models.MachineStatus.available):
        return None

    # 获取机房费率
    room = get_computer_room(db, machine.room_id)
    assert room is not None

    # 创建上机会话
    db_session = models.ComputerSession(
        student_id=session_data.student_id,
        machine_id=session_data.machine_id,
        start_time=datetime.now(),
        hourly_rate=room.hourly_rate,
        status=models.SessionStatus.active,
    )

    # 更新机器状态
    machine.status = models.MachineStatus.in_use
    room.available_computers -= 1

    db.add(db_session)
    db.commit()
    db.refresh(db_session)
    return db_session


def end_computer_session(db: Session, session_id: int):
    session = (
        db.query(models.ComputerSession)
        .filter(models.ComputerSession.id_ == session_id)
        .first()
    )
    if not session or session.status != models.SessionStatus.active:
        return None

    # 计算费用
    session.end_time = datetime.now()
    duration = (session.end_time - session.start_time).total_seconds() / 60  # 分钟
    session.duration_minutes = int(duration)
    session.total_cost = round((duration / 60) * session.hourly_rate, 2)
    session.status = models.SessionStatus.completed

    # 扣费
    student = get_student(db, session.student_id)
    assert student is not None
    student.balance -= session.total_cost

    # 恢复机器状态
    machine = (
        db.query(models.Machine)
        .filter(models.Machine.id_ == session.machine_id)
        .first()
    )
    assert machine is not None
    machine.status = models.MachineStatus.available

    room = get_computer_room(db, machine.room_id)
    assert room is not None
    room.available_computers += 1

    db.commit()
    return session


def get_active_sessions(db: Session):
    return (
        db.query(models.ComputerSession)
        .filter(models.ComputerSession.status == models.SessionStatus.active)
        .all()
    )


def get_student_sessions(db: Session, student_id: int):
    return (
        db.query(models.ComputerSession)
        .filter(models.ComputerSession.student_id == student_id)
        .order_by(models.ComputerSession.created_at.desc())
        .all()
    )


def get_session_history(db: Session, skip: int, limit: int):
    results = (
        db.query(
            models.ComputerSession,
            models.Student.student_id,
            models.Student.name,
            models.ComputerRoom.name,
            models.Machine.machine_number,
        )
        .join(models.Student, models.Student.id_ == models.ComputerSession.student_id)
        .join(models.Machine, models.Machine.id_ == models.ComputerSession.machine_id)
        .join(models.ComputerRoom, models.ComputerRoom.id_ == models.Machine.room_id)
        .filter(
            models.ComputerSession.status == models.SessionStatus.completed,
        )
        .order_by(models.ComputerSession.created_at.desc())
        .offset(skip)
        .limit(limit)
        .all()
    )

    session_history_items = []
    for result in results:
        session, student_number, student_name, room_name, machine_number = result
        session_history_item = schemas.SessionHistoryItem(
            id_=session.id_,
            student_id=session.student_id,
            machine_id=session.machine_id,
            start_time=session.start_time,
            end_time=session.end_time,
            duration_minutes=session.duration_minutes,
            total_cost=session.total_cost,
            hourly_rate=session.hourly_rate,
            status=session.status,
            created_at=session.created_at,
            student_number=student_number,
            student_name=student_name,
            room_name=room_name,
            machine_number=machine_number,
        )
        session_history_items.append(session_history_item)

    return session_history_items
